import os
import sys
import django

base_dir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
sys.path.append(base_dir)
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "bigSDP.settings")
django.setup()
from scripts import base
from analysis import models

"""爬取数据分析html页面数据"""
import scrapy
import xlwt, xlrd
import time
import os
from datetime import datetime
import pymysql


# scrapy runspider addCompareData.py  运行指令

class sqlTool(object):
    def __init__(self):
        """配置 数据库信息"""
        self.conn = pymysql.connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            passwd='123456',
            database='bigsdp',
            charset='utf8'
        )

        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        """
        cursor=pymysql.cursors.DictCursor
        将查询结果以字典的形式返回
        """

    def execsql(self, sql):
        self.cursor.execute(sql)
        self.conn.commit()

class ReadExcel(object):
    """读取excel时间数据，做转化"""

    def __init__(self, sheetname='数据分析'):
        """
        初始化方法

        Args:
            data_path (str): 文件路径
            sheetname (str): 工作表名称

        Returns:
            None

        """
        # 定义一个属性接收文件路径
        self.data_path = 'D:\\workspace\\temp\\case\\大屏数字人指令集-数据分析.xlsx'
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows

    def readExcel(self,num):
        """
        从Excel文件中读取信息

        Args:
            无参数，函数内部没有使用任何参数。

        Returns:
            无返回值，仅执行操作。
        """
        cmd = self.table.cell_value(num, 2)
        return cmd


class MySpider(scrapy.Spider):
    """爬取html页码"""
    name = 'myspider'
    start_urls = [
        'file:\\\D:/workspace\\temp\\case\\%E6%95%B0%E6%8D%AE%E5%88%86%E6%9E%90%20-%20%E5%89%AF%E6%9C%AC.html', ]

    def analysis_comparedata(self, type, chart, tts, sqlval, data, version, cmd_id):
        sql = "insert into analysis_comparedata values(NULL,"
        sql += "'{}',".format(type)
        sql += "'{}',".format(chart)
        sql += "'{}',".format(tts)
        sql += '"{}",'.format(sqlval)
        sql += "'{}',".format(data)
        sql += "NULL,"
        sql += "'{}',".format(version)
        sql += "'{}');".format(cmd_id)
        # print(sql)

        return sql

    def tts_sse(self, response, count):
        # 流式部分的调整开始START：
        divNum = response.css(
            'body > div > details:nth-child({}) > dl > div'.format(count)).getall()
        data = []
        resTimeList = []
        for num in range(5, len(divNum) + 1):  # 从 CHART TTS 请求发起开始判断
            rowText = response.css(
                'body > div > details:nth-child({}) > dl > div:nth-child({}) > dt::text'.format(
                    count, num)).get()
            if "CHART TTS 数据响应" in rowText:
                # 拿到响应时间
                chartContentTime = rowText.split('- ')[1]
                resTimeList.append(chartContentTime)
                # 拿到响应内容
                res = response.css(
                    'body > div > details:nth-child({}) > dl > div:nth-child({}) > dd > pre:'
                    ':text'.format(count, num)).get()
                indexStart = res.find('"data"')
                indexEnd = res.find('",', indexStart + 9)
                chartContentList = res[indexStart + 9:indexEnd:1].replace('\n', '')
                data.append(chartContentList)
            if "复杂闲聊 完整文本" in rowText:
                RD_text = response.css(
                    'body > div > details:nth-child({}) > dl > div:nth-child({}) > dd::text'
                    ''.format(count, num)).get()

        tts = ''.join(data)
        # writeObj.write_excel_body(count, 24, tts)
        data.clear()

        return tts

    def parse(self, response):
        """定义获取数据分析页面的方法"""
        sqlObj = sqlTool()
        obj = ReadExcel()
        ttsType = 'stream'  # or noStream
        links = response.css('body > div > details').getall()
        for count in range(1, len(links) + 1):
            if ttsType == 'noStream':
                cmd = \
                    response.css('body > div > details:nth-child({}) > summary::text'.format(count)).get().split(
                        '：')[1]
                cmd1 = response.css('body > div > details:nth-child({}) > summary::text'.format(count)).get()
                if cmd1 == "指令：" or cmd1.startswith("指令：{"):  # 语音输入为空
                    continue
                else:
                    midWareReturn = response.css(
                        'body > div > details:nth-child({}) > dl > div:nth-child(4) > dd > pre::text'.format(
                            count)).get()
                    index_type = midWareReturn.find('type')
                    index_typeEnd = midWareReturn.find('"', index_type + 8)
                    if midWareReturn[index_type + 8:index_typeEnd] == '数据分析':
                        notNull = response.css(
                            'body > div > details:nth-child({}) > dl > div:nth-child(6) > dd > pre::text'.format(
                                count)).get()
                        print(notNull)
                        if notNull:
                            resAnalysisContent = response.css(
                                'body > div > details:nth-child({}) > dl > div:nth-child(6) > dd > pre::text'
                                ''.format(count)).get()
                            index_execSql = resAnalysisContent.find('执行的SQL语句",')
                            if index_execSql != -1:
                                index_execSql_time = resAnalysisContent.find('timestamp', index_execSql)
                                execSql_time = resAnalysisContent[index_execSql_time + 13:index_execSql_time + 26:1]
                                print('执行的SQL语句：', execSql_time)
                            else:
                                print('执行的SQL语句：空')

                            index_execSql = resAnalysisContent.find('SQL执行结果返回条数",')
                            if index_execSql != -1:
                                index_execSql_time = resAnalysisContent.find('timestamp', index_execSql)
                                reqChart_time = resAnalysisContent[
                                                index_execSql_time + 13:index_execSql_time + 26:1]
                                resChart_time = reqChart_time  # 如果sql执行结果返回0，将生成图表时间配置为0
                                print('SQL执行结果返回条数', reqChart_time)

                            index_reqChart = resAnalysisContent.find('请求图表分析",')
                            if index_reqChart != -1:
                                index_reqChart_time = resAnalysisContent.find('timestamp', index_reqChart)
                                reqChart_time = resAnalysisContent[
                                                index_reqChart_time + 13:index_reqChart_time + 26:1]
                                print('请求图表分析：', reqChart_time)
                            else:
                                print('请求图表分析：空')

                            index_resChart = resAnalysisContent.find('图表分析返回",')
                            if index_resChart != -1:
                                index_resChart_time = resAnalysisContent.find('timestamp', index_resChart)
                                resChart_time = resAnalysisContent[
                                                index_resChart_time + 13:index_resChart_time + 26:1]
                                print('图表分析返回：', resChart_time)
                            else:
                                print('图表分析返回：空')
            else:
                "tts流式输出"
                cmd = \
                    response.css('body > div > details:nth-child({}) > summary::text'.format(count)).get().split(
                        '：')[1]

                midWareReturn = response.css(
                    'body > div > details:nth-child({}) > dl > div:nth-child(4) > dd > pre::text'.format(
                        count)).get()
                index_type = midWareReturn.find('type')
                index_typeEnd = midWareReturn.find('"', index_type + 8)
                if midWareReturn[index_type + 8:index_typeEnd] == '数据分析':
                    notNull = response.css(
                        'body > div > details:nth-child({}) > dl > div:nth-child(6) > dd > pre::text'.format(
                            count)).get()
                    # print(notNull)
                    if notNull:
                        resAnalysisContent = response.css(
                            'body > div > details:nth-child({}) > dl > div:nth-child(6) > dd > pre::text'
                            ''.format(count)).get()

                        index_genSql = resAnalysisContent.find('生成SQL返回",')
                        if index_genSql != -1:
                            sqlValIndexStart = resAnalysisContent.find('text', index_genSql)
                            sqlValIndexEnd = resAnalysisContent.find('",', sqlValIndexStart)
                            sqlVal = resAnalysisContent[sqlValIndexStart + 8:sqlValIndexEnd:1]
                            # print(sqlVal)
                            if sqlVal.startswith('SELECT'):
                                # print('执行的SQL语句是：', sqlVal)
                                pass
                            else:
                                continue
                        else:
                            print('执行的SQL语句是：空')
                            continue

                        index_chartType = resAnalysisContent.find('chartType')
                        if index_chartType != -1:
                            chartIndexEnd = resAnalysisContent.find('",', index_chartType)
                            chartVal = resAnalysisContent[index_chartType + 13:chartIndexEnd:1]
                            # print('图表类型是：', chartVal)
                            if chartVal == '未知':
                                continue
                        else:
                            print('图表类型是：空')
                            continue

                        index_execSql = resAnalysisContent.find('执行的SQL语句')
                        if index_execSql != -1:
                            dataIndexStart = resAnalysisContent.find('data', index_execSql)
                            dataIndexEnd = resAnalysisContent.find('}",', dataIndexStart)
                            dataVal = resAnalysisContent[
                                      dataIndexStart + 7:dataIndexEnd:1]
                            # print('data数据是：', dataVal)
                        else:
                            print('data数据是：空')
                            continue

                        tts = self.tts_sse(response, count)


                        # 强制使用excel中的指令
                        cmd = obj.readExcel(count)
                        base_obj = models.BaseData.objects.filter(cmd=cmd).first()
                        print(cmd)
                        print(base_obj)
                        if base_obj:
                            # 新增case：方式1
                            instance = models.CompareData.objects.create(recv_type='数据分析', recv_chart=chartVal,
                                                                         recv_tts=tts, recv_sql=sqlVal,
                                                                         recv_data=dataVal, version='V1',
                                                                         cmd=base_obj)
                            # 新增case：方式2
                            # fullsql = self.analysis_comparedata('数据分析', chartVal, tts, sqlVal, dataVal,
                            #                                  version='基准case', cmd_id=obj.id)
                            # print(fullsql)
                            #
                            # sqlObj.cursor.execute(fullsql)
        # sqlObj.conn.commit()

